ALTER TABLE purchase DROP CONSTRAINT purchase_id_task_fkey;
ALTER TABLE purchase DROP COLUMN id_task;

ALTER TABLE purchase ADD COLUMN id_checklist integer;
ALTER TABLE purchase ADD COLUMN id_task integer;

ALTER TABLE purchase ADD CONSTRAINT purchase_id_checklist_fkey FOREIGN KEY (id_checklist)

      REFERENCES checklist (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE purchase ADD CONSTRAINT purchase_id_task_fkey FOREIGN KEY (id_task)

      REFERENCES checklist_item (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION;
	  
DROP FUNCTION sp_purchase_count(integer, character varying, character varying, character varying, integer, numeric, numeric, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, boolean, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone, character varying, integer, integer, integer, integer, numeric, numeric, timestamp without time zone, timestamp without time zone, integer, integer);
DROP FUNCTION sp_purchase_insert(character varying, character varying, character varying, integer, numeric, numeric, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, boolean, integer, boolean, integer, integer, character varying, integer, integer, integer, integer, numeric, numeric, timestamp without time zone, integer, integer);
DROP FUNCTION sp_purchase_selectlist(integer, integer, integer, character varying, character varying, character varying, integer, numeric, numeric, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, boolean, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone, character varying, integer, integer, integer, integer, numeric, numeric, timestamp without time zone, timestamp without time zone, integer, integer);
DROP FUNCTION sp_purchase_update(integer, character varying, character varying, character varying, integer, numeric, numeric, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, boolean, integer, boolean, integer, integer, boolean, timestamp without time zone, character varying, integer, integer, integer, integer, numeric, numeric, timestamp without time zone, integer, integer);


CREATE OR REPLACE FUNCTION sp_purchase_insert(
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_plan_cost	numeric DEFAULT null,
	_real_cost	numeric DEFAULT null,
	_plan_order_time	timestamp without time zone DEFAULT null,
	_plan_delivery_time	timestamp without time zone DEFAULT null,
	_real_order_tate	timestamp without time zone DEFAULT null,
	_real_delivery_time	timestamp without time zone DEFAULT null,
	_need_approval	boolean DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_creator	integer DEFAULT null,
	_remark	character varying DEFAULT null,
	_purchaser	integer DEFAULT null,
	_supplier	integer DEFAULT null,
	_id_currency	integer DEFAULT null,
	_id_project	integer DEFAULT null,
	_discount	numeric DEFAULT null,
	_additional_cost	numeric DEFAULT null,
	_order_time	timestamp without time zone DEFAULT null,
	_id_quotation	integer DEFAULT null,
	_id_quotation_item	integer DEFAULT null,
	_id_checklist	integer DEFAULT null,
	_id_task	integer DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO purchase(name,code,description,plan_cost,real_cost,plan_order_time,plan_delivery_time,real_order_tate,real_delivery_time,need_approval,status,is_draft,notification,creator,updator,deletion_time,remark,purchaser,supplier,id_currency,id_project,discount,additional_cost,order_time,id_quotation,id_quotation_item,id_checklist,id_task)
	VALUES(_name,_code,_description,_plan_cost,_real_cost,_plan_order_time,_plan_delivery_time,_real_order_tate,_real_delivery_time,_need_approval,_status,_is_draft,_notification,_creator,_creator,null,_remark,_purchaser,_supplier,_id_currency,_id_project,_discount,_additional_cost,_order_time,_id_quotation,_id_quotation_item,_id_checklist,_id_task);
RETURN currval('purchase_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_purchase_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_plan_cost	numeric DEFAULT null,
	_real_cost	numeric DEFAULT null,
	_plan_order_timeFrom	timestamp without time zone DEFAULT null,
	_plan_order_timeTo	timestamp without time zone DEFAULT null,
	_plan_delivery_timeFrom	timestamp without time zone DEFAULT null,
	_plan_delivery_timeTo	timestamp without time zone DEFAULT null,
	_real_order_tateFrom	timestamp without time zone DEFAULT null,
	_real_order_tateTo	timestamp without time zone DEFAULT null,
	_real_delivery_timeFrom	timestamp without time zone DEFAULT null,
	_real_delivery_timeTo	timestamp without time zone DEFAULT null,
	_need_approval	boolean DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_remark	character varying DEFAULT null,
	_purchaser	integer DEFAULT null,
	_supplier	integer DEFAULT null,
	_id_currency	integer DEFAULT null,
	_id_project	integer DEFAULT null,
	_discount	numeric DEFAULT null,
	_additional_cost	numeric DEFAULT null,
	_order_timeFrom	timestamp without time zone DEFAULT null,
	_order_timeTo	timestamp without time zone DEFAULT null,
	_id_quotation	integer DEFAULT null,
	_id_quotation_item	integer DEFAULT null,
	_id_checklist	integer DEFAULT null,
	_id_task	integer DEFAULT null
)
RETURNS SETOF purchase
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM purchase WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_plan_cost is null OR plan_cost = _plan_cost)	
	AND (_real_cost is null OR real_cost = _real_cost)	
	AND (_plan_order_timeFrom is null OR plan_order_time >= _plan_order_timeFrom)
	AND	(_plan_order_timeTo is null OR plan_order_time <= _plan_order_timeTo)	
	AND (_plan_delivery_timeFrom is null OR plan_delivery_time >= _plan_delivery_timeFrom)
	AND	(_plan_delivery_timeTo is null OR plan_delivery_time <= _plan_delivery_timeTo)	
	AND (_real_order_tateFrom is null OR real_order_tate >= _real_order_tateFrom)
	AND	(_real_order_tateTo is null OR real_order_tate <= _real_order_tateTo)	
	AND (_real_delivery_timeFrom is null OR real_delivery_time >= _real_delivery_timeFrom)
	AND	(_real_delivery_timeTo is null OR real_delivery_time <= _real_delivery_timeTo)	
	AND (_need_approval is null OR need_approval = _need_approval)	
	AND (_status is null OR status = _status)	
	AND (_is_draft is null OR is_draft = _is_draft)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_remark is null OR remark = _remark)	
	AND (_purchaser is null OR purchaser = _purchaser)	
	AND (_supplier is null OR supplier = _supplier)	
	AND (_id_currency is null OR id_currency = _id_currency)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_discount is null OR discount = _discount)	
	AND (_additional_cost is null OR additional_cost = _additional_cost)	
	AND (_order_timeFrom is null OR order_time >= _order_timeFrom)
	AND	(_order_timeTo is null OR order_time <= _order_timeTo)	
	AND (_id_quotation is null OR id_quotation = _id_quotation)	
	AND (_id_quotation_item is null OR id_quotation_item = _id_quotation_item)	
	AND (_id_checklist is null OR id_checklist = _id_checklist)	
	AND (_id_task is null OR id_task = _id_task)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_purchase_count (
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_plan_cost	numeric DEFAULT null,
	_real_cost	numeric DEFAULT null,
	_plan_order_timeFrom	timestamp without time zone DEFAULT null,
	_plan_order_timeTo	timestamp without time zone DEFAULT null,
	_plan_delivery_timeFrom	timestamp without time zone DEFAULT null,
	_plan_delivery_timeTo	timestamp without time zone DEFAULT null,
	_real_order_tateFrom	timestamp without time zone DEFAULT null,
	_real_order_tateTo	timestamp without time zone DEFAULT null,
	_real_delivery_timeFrom	timestamp without time zone DEFAULT null,
	_real_delivery_timeTo	timestamp without time zone DEFAULT null,
	_need_approval	boolean DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_remark	character varying DEFAULT null,
	_purchaser	integer DEFAULT null,
	_supplier	integer DEFAULT null,
	_id_currency	integer DEFAULT null,
	_id_project	integer DEFAULT null,
	_discount	numeric DEFAULT null,
	_additional_cost	numeric DEFAULT null,
	_order_timeFrom	timestamp without time zone DEFAULT null,
	_order_timeTo	timestamp without time zone DEFAULT null,
	_id_quotation	integer DEFAULT null,
	_id_quotation_item	integer DEFAULT null,
	_id_checklist	integer DEFAULT null,
	_id_task	integer DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM purchase WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_plan_cost is null OR plan_cost = _plan_cost)	
	AND (_real_cost is null OR real_cost = _real_cost)	
	AND (_plan_order_timeFrom is null OR plan_order_time >= _plan_order_timeFrom)
	AND	(_plan_order_timeTo is null OR plan_order_time <= _plan_order_timeTo)	
	AND (_plan_delivery_timeFrom is null OR plan_delivery_time >= _plan_delivery_timeFrom)
	AND	(_plan_delivery_timeTo is null OR plan_delivery_time <= _plan_delivery_timeTo)	
	AND (_real_order_tateFrom is null OR real_order_tate >= _real_order_tateFrom)
	AND	(_real_order_tateTo is null OR real_order_tate <= _real_order_tateTo)	
	AND (_real_delivery_timeFrom is null OR real_delivery_time >= _real_delivery_timeFrom)
	AND	(_real_delivery_timeTo is null OR real_delivery_time <= _real_delivery_timeTo)	
	AND (_need_approval is null OR need_approval = _need_approval)	
	AND (_status is null OR status = _status)	
	AND (_is_draft is null OR is_draft = _is_draft)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_remark is null OR remark = _remark)	
	AND (_purchaser is null OR purchaser = _purchaser)	
	AND (_supplier is null OR supplier = _supplier)	
	AND (_id_currency is null OR id_currency = _id_currency)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_discount is null OR discount = _discount)	
	AND (_additional_cost is null OR additional_cost = _additional_cost)	
	AND (_order_timeFrom is null OR order_time >= _order_timeFrom)
	AND	(_order_timeTo is null OR order_time <= _order_timeTo)	
	AND (_id_quotation is null OR id_quotation = _id_quotation)	
	AND (_id_quotation_item is null OR id_quotation_item = _id_quotation_item)	
	AND (_id_checklist is null OR id_checklist = _id_checklist)	
	AND (_id_task is null OR id_task = _id_task);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_purchase_update 
(	_id integer,
	_name character varying(255),
	_code character varying(20),
	_description character varying,
	_plan_cost numeric,
	_real_cost numeric,
	_plan_order_time timestamp without time zone,
	_plan_delivery_time timestamp without time zone,
	_real_order_tate timestamp without time zone,
	_real_delivery_time timestamp without time zone,
	_need_approval boolean,
	_status integer,
	_is_draft boolean,
	_notification integer,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone,
	_remark character varying,
	_purchaser integer,
	_supplier integer,
	_id_currency integer,
	_id_project integer,
	_discount numeric,
	_additional_cost numeric,
	_order_time timestamp without time zone,
	_id_quotation integer,
	_id_quotation_item integer,
	_id_checklist integer,
	_id_task integer  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE purchase 
	SET 		name = _name,
		code = _code,
		description = _description,
		plan_cost = _plan_cost,
		real_cost = _real_cost,
		plan_order_time = _plan_order_time,
		plan_delivery_time = _plan_delivery_time,
		real_order_tate = _real_order_tate,
		real_delivery_time = _real_delivery_time,
		need_approval = _need_approval,
		status = _status,
		is_draft = _is_draft,
		notification = _notification,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time,
		remark = _remark,
		purchaser = _purchaser,
		supplier = _supplier,
		id_currency = _id_currency,
		id_project = _id_project,
		discount = _discount,
		additional_cost = _additional_cost,
		order_time = _order_time,
		id_quotation = _id_quotation,
		id_quotation_item = _id_quotation_item,
		id_checklist = _id_checklist,
		id_task = _id_task
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
